/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.campaign.dao;

import com.aptech.campaign.entities.Account;
import com.aptech.campaign.util.ConfigureDB;
import com.sun.rowset.CachedRowSetImpl;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.rowset.CachedRowSet;
import sun.misc.BASE64Encoder;

/**
 *
 * @author Tai Nguyen
 */
public class ManagementDAO {

    private String lasterror;
    String sql;
    Connection con = null;
    ConfigureDB db = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    CachedRowSet crs = null;

    public ManagementDAO() {
        db = new ConfigureDB();
    }

    /**
     * login to system
     * @param String userName, String password
     * @return the boolean value
     */
    public boolean login(String userName, String password) {
        con = db.getConnection();
        sql = "select * from [Account] where UserName = ? and Password = ?";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, userName);
            pst.setString(2, encrypt(password));
            rs = pst.executeQuery();
            if (rs.next()) {
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ManagementDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     * add Account into Permission table with default role is USER
     * @param userName
     * @return the boolean value
     */
    public boolean createPermission(String userName) {
        con = db.getConnection();
        sql = "insert into [Permission] values (?, ?, ?)";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, userName);
            pst.setInt(2, 1);
            pst.setString(3, "USER");
            if (pst.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException ex) {
            setLasterror("Fail!" + ex.getMessage());
            return false;
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     * create a new account
     * @param Account acc
     * @return the boolean value
     */
    public boolean createAccount(Account acc) {
        if (!checkAccount(acc.getUserName())) {
            setLasterror("userName already Exist!");
            return false;
        }
        con = db.getConnection();
        try {

            sql = "insert into Account values(?, ?, ?, ?, ?, ?)";
            pst = con.prepareStatement(sql);
            pst.setString(1, acc.getUserName());
            pst.setString(2, encrypt(acc.getPassword()));
            pst.setString(3, acc.getFullName());
            pst.setDate(4, acc.getBirthday());
            pst.setString(5, acc.getFone());
            pst.setString(6, acc.getGender());
            if (pst.executeUpdate() > 0) {
                if (createPermission(acc.getUserName())) {
                    setLasterror("Add Account Successful");
                    return true;
                }
            }
        } catch (SQLException ex) {
            setLasterror("" + ex.getMessage());
            return false;
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     * change permission of user
     * @param String userName, int permisstionID, String permissionName
     * @return the boolean value
     */
    public boolean changePermission(String userName, int permissionID, String permissionName) {
        con = db.getConnection();
        sql = "update Permission set PermissionID = ?, PermissionName = ? where UserName = ?";
        try {
            pst = con.prepareStatement(sql);
            pst.setInt(1, permissionID);
            pst.setString(2, permissionName);
            pst.setString(3, userName);
            if (pst.executeUpdate() > 0) {
                setLasterror("Change Permission Successful!");
                return true;
            }
        } catch (SQLException ex) {
            setLasterror("FAIL! " + ex.getMessage());
            return false;
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     * get permissionName
     * @param String userName
     * @return String PermissionName
     */
    public String readPermissionName(String userName) {
        String permissionName = null;
        con = db.getConnection();
        sql = "select PermissionName from [Permission] where UserName = ?";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, userName);
            rs = pst.executeQuery();
            while (rs.next()) {
                permissionName = rs.getString(1);
                return permissionName;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ManagementDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return null;

    }

    /**
     * change password
     * @param String userName, String oldPassword, String newPassword, String confirmNewPass
     * @return boolean value
     */
    public boolean updatePassword(String userName, String oldPassword, String newPassword, String confirmNewPass) {
        if (newPassword.length() < 6) {
            setLasterror("Password has minimum 6 letter!");
            return false;
        }
        if (!newPassword.equals(confirmNewPass)) {
            setLasterror("Confirm password incorrect!");
            return false;
        }
        if (!login(userName, oldPassword)) {
            setLasterror("oldPassword incorrect!");
            return false;
        }
        con = db.getConnection();
        sql = "update [Account] set Password = ? where UserName = ?";
        try {

            pst = con.prepareStatement(sql);
            pst.setString(1, encrypt(newPassword));
            pst.setString(2, userName);
            if (pst.executeUpdate() > 0) {
                setLasterror("Successful!");
                return true;
            }
        } catch (SQLException ex) {
            setLasterror(ex.getMessage());
            return false;
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     * change information of user
     * @param String userName
     * @return the boolean value
     */
    public boolean updateProfile(String userName, String fullName, Date birthday, String phone, String gender) {
        con = db.getConnection();
        sql = "update [Account] set FullName = ?, Birthday = ?, Phone = ?, Gender = ? where UserName = ?";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1, fullName);
            pst.setDate(2, birthday);
            pst.setString(3, phone);
            pst.setString(4, gender);
            pst.setString(5, userName);
            if (pst.executeUpdate() > 0) {
                setLasterror("Update successful!");
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ManagementDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return false;
    }

    /**
     * get array of information of account in database
     * @param
     * @return an Arraylist<>
     */
    public ArrayList<Account> readByAllAccount() {
        ArrayList<Account> arr = new ArrayList<Account>();
        con = db.getConnection();
        try {

            sql = "select * from [Account]";
            crs = new CachedRowSetImpl();
            crs.setCommand(sql);
            crs.execute(con);
            while (crs.next()) {
                Account acc = new Account();
                acc.setUserName(crs.getString(1));
                acc.setFullName(crs.getString(3));
                acc.setBirthday(crs.getDate(4));
                acc.setFone(crs.getString(5));
                acc.setGender(crs.getString(6));
                arr.add(acc);

            }
        } catch (SQLException ex) {
            Logger.getLogger(ManagementDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return arr;
    }

    /**
     * check exist of account
     * @param String userName
     * @return the boolean value
     */
    public boolean checkAccount(String userName) {
        con = db.getConnection();
        try {

            sql = "select * from [Account] where UserName = ?";
            pst = con.prepareStatement(sql);
            pst.setString(1, userName);
            rs = pst.executeQuery();
            if (rs.next()) {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ManagementDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            db.closeConnection(con);
        }
        return true;
    }

    /**
     * Encrypt a string by MD5
     * @param String str
     * @return the String value
     */
    public String encrypt(String str) {
        try {
            MessageDigest md = MessageDigest.getInstance("MD5");
            byte b[] = md.digest(str.getBytes());
            BASE64Encoder encoder = new BASE64Encoder();
            String base64 = encoder.encode(b);
            return base64;
        } catch (NoSuchAlgorithmException ex) {
            Logger.getLogger(ManagementDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    /**
     * @return the lasterror
     */
    public String getLasterror() {
        return lasterror;
    }

    /**
     * @param lasterror the lasterror to set
     */
    public void setLasterror(String lasterror) {
        this.lasterror = lasterror;
    }
}
